<?
session_start();
error_reporting(E_ALL);
include('conn.php');

$ch = $_REQUEST['q'];
if($ch=='2'){
	$qry1="SELECT distinct custid from opbalmast where custid in(select custid from customermast where custtype='Vendor');";
	$qry = mysql_query($qry1);
	$data = array();
	$i = 0;
	while($rows = mysql_fetch_array($qry)){
		$qry2="SELECT custdesc from customermast where custid='".$rows['custid']."' ";
		$rows2 = mysql_fetch_array(mysql_query($qry2));
		$data[$i]=$rows2['custdesc'];
		$i++;
	}
	echo json_encode($data);
}
else{
//$custid = $_SESSION['custid'];
//$custid = 484; // rajesh- test
$custid = 423; // AKBAR ONLINE
$selc = $_REQUEST['selc'];
$frdate = $_REQUEST['from'];
$todate = $_REQUEST['to'];
$qry1="SELECT custid from customermast where custdesc ='$selc';";
$qry = mysql_query($qry1);
$rows = mysql_fetch_array($qry);
$custid=$rows['custid'];
	
	
	// ############################## old query backup 27th Dec -- Uncomment if its not working. ##############################

// $qry1="SELECT n.custid,n.opbaldate, SUM(o.amount) As running_total FROM (select tt.opbaldate, sum(tt.debit) as amount, tt.custid from
// (select trndate as opbaldate,netrate*-1 as debit,venid as custid , 't' as flag
// from transactionmast where netrate >0 and trndate >= (select min(opbaldate) from opbalmast where  amtreceived > 0 and trndate between '$frdate' and '$todate') 
// union all
// (select opbaldate,amtreceived,custid, 'c' as flag from opbalmast where 
// amtreceived >0 and opbaldate between '$frdate' and '$todate')
// union all
// (select opbaldate,amtpaid * -1 as debit,custid, 'd' as flag from opbalmast 
// where amtpaid >0 and opbaldate between '$frdate' and '$todate')
// order by custid,opbaldate) tt
// group by custid,opbaldate
// ) n INNER JOIN (select tt.opbaldate, sum(tt.debit) as amount, tt.custid from
// (select trndate as opbaldate,netrate*-1 as debit,venid as custid , 't' as flag
// from transactionmast where netrate >0 and trndate >= (select min(opbaldate) from opbalmast 
// where amtreceived > 0 and trndate between '$frdate' and '$todate') 
// union all
// (select opbaldate,amtreceived,custid, 'c' as flag from opbalmast 
// where amtreceived >0 and opbaldate between '$frdate' and '$todate')
// union all
// (select opbaldate,amtpaid * -1 as debit,custid, 'd' as flag from opbalmast 
// where amtpaid >0 and opbaldate between '$frdate' and '$todate')
// order by custid,opbaldate) tt
// group by custid,opbaldate
// )
// o ON (n.custid = o.custid) and (n.opbaldate >= o.opbaldate) GROUP BY n.custid, n.opbaldate
// order by n.custid, n.opbaldate;";

$qry1="SELECT n.custid,n.opbaldate, SUM(o.amount) As running_total FROM (
select tt.opbaldate, sum(tt.debit) as amount, tt.custid from
	(
		select trnid as tranid,trndate as opbaldate, venid3 as custid, netrate3*-1 as debit,'n3' as flag from transactionmast  where status =0 and venid3 > 0  and netrate3 > 0
		and trndate between '$frdate' and '$todate'
		union all
		select trnid as tranid,trndate as opbaldate, venid2 as custid, netrate2*-1 as debit,'n2' as flag from transactionmast  where status =0 and venid2 > 0  and netrate2 > 0  and trndate between '$frdate' and '$todate'
		union all
		select  trnid as tranid,trndate as opbaldate, venid as custid, netrate*-1 as debit,'n1' as flag from transactionmast   where  status =0 and venid > 0  and netrate > 0  and trndate between '$frdate' and '$todate'
		union all
		(select opbaltranid as tranid, opbaldate, custid,amtreceived as debit,'o1' as flag from opbalmast where amtreceived > 0   and opbaldate between '$frdate' and '$todate')
		union all
		(select opbaltranid as tranid,opbaldate,custid,amtpaid * -1 as debit,'o2' as flag from opbalmast where amtpaid > 0  and opbaldate between '$frdate' and '$todate')
		
		
	) tt
	group by custid,opbaldate
	order by custid,opbaldate
) n INNER JOIN 
(
select tt.opbaldate, sum(tt.debit) as amount, tt.custid from
	(
		select trnid as tranid,trndate as opbaldate, venid3 as custid, netrate3*-1 as debit,'n3' as flag from transactionmast  where  status =0 and venid3 > 0  and netrate3 > 0
		
		and trndate between '$frdate' and '$todate'
		union all
		select trnid as tranid,trndate as opbaldate, venid2 as custid, netrate2*-1 as debit,'n2' as flag from transactionmast  where  status =0 and venid2 > 0  and netrate2 > 0  and trndate between '$frdate' and '$todate'
		union all
		select  trnid as tranid,trndate as opbaldate, venid as custid, netrate*-1 as debit,'n1' as flag from transactionmast   where  status =0 and venid > 0  and netrate > 0  and trndate between '$frdate' and '$todate'
		union all
		(select opbaltranid as tranid, opbaldate, custid,amtreceived as debit,'o1' as flag from opbalmast where amtreceived > 0   and opbaldate between '$frdate' and '$todate')
		union all
		(select opbaltranid as tranid,opbaldate,custid,amtpaid * -1 as debit,'o2' as flag from opbalmast where amtpaid > 0  and opbaldate between '$frdate' and '$todate')
		
		
	) tt
	group by custid,opbaldate
	order by custid,opbaldate
)
o ON (n.custid = o.custid) and (n.opbaldate >= o.opbaldate) 
GROUP BY n.custid, n.opbaldate
order by n.custid, n.opbaldate";

	$qry = mysql_query($qry1);
	$data = array();
	$i=1;	
	
	while($rows = mysql_fetch_array($qry))
	{
		$qrycust = "select custdesc from customermast where custid='".$rows['custid']."';";
		$rowcust = mysql_fetch_array(mysql_query($qrycust));
		
		$data[] = array("Sr_NO" => $i++,        			
						"rqdate" => $rows['opbaldate'],
						"custid" => $rows['custid'],
						"customer" => $rowcust['custdesc'],
						"runningtotal" =>$rows['running_total']
						);
	}
	echo json_encode($data);
}
?>